using System;
using System.Collections.Generic;
using System.Text;
using AAM.SQL;
using System.Configuration;
using System.Reflection;
using System.Data.Common;
using AAM.BIZ;
using Logging;

namespace AAM.DAL
{
    public abstract class SqlBizObjectProvider<T> : BizObjectProvider<T>
    {

        private static Logger Logger = Logger.GetLogger("AAM.DAL.SqlBizObjectProvider");

        public static DatabaseTable table = null;
        public static List<PropertyInfo> properties = null;
        public static List<DatabaseRow> rows = null;

        private static string deleteCommandString = null;
        private static string updateCommandString = null;
        private static string insertCommandString = null;
        private static string selectCommandString = null;

        private Connection connection;

        public static DatabaseTable Table
        {
            get
            {
                if (table == null) table = DatabaseTable.GetDatabaseTable(typeof(T));
                return table;
            }
        }
        public static List<PropertyInfo> Properties
        {
            get
            {
                if (properties == null) 
                {
                    properties = new List<PropertyInfo>();
                    rows = DatabaseRow.GetDatabaseRows(typeof(T), ref properties);
                }
                return properties;
            }
        }
        public static List<DatabaseRow> Rows
        {
            get
            {
                if (rows == null)
                {
                    properties = new List<PropertyInfo>();
                    rows = DatabaseRow.GetDatabaseRows(typeof(T), ref properties);
                }
                return rows;
            }
        }

        public static string GetTableAlias()
        {
            DatabaseTable table = Table;
            if(table == null) throw new Exception();
            if (!string.IsNullOrEmpty(table.TableAlias)) return table.TableAlias;
            if (!string.IsNullOrEmpty( table.TableName )) return table.TableName;
            return typeof(T).Name;
        }
        public static string GetTableName()
        {
            DatabaseTable table = Table;
            if (table == null) throw new Exception();
            if (!string.IsNullOrEmpty(table.TableName)) return table.TableName;
            return typeof(T).Name;
        }
        public static string GetAllRows(string prefix)
        {
            bool withPrefix = !string.IsNullOrEmpty(prefix);
            DatabaseTable table = Table;
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            StringBuilder builder = new StringBuilder();
            bool coma = false;
            for (int c=0; c<rows.Count; c++)
            {
                if (coma) builder.Append(", ");
                else coma = true;
                string name = properties[c].Name;
                if (!string.IsNullOrEmpty(rows[c].RowName)) name = rows[c].RowName;
                if (withPrefix) builder.Append(prefix + ".");
                builder.Append(name);
            }
            return builder.ToString();
        }

        static SqlBizObjectProvider()
        {
            try
            {
                deleteCommandString = BuildDeleteCommandString();
                updateCommandString = BuildUpdateCommandString();
                insertCommandString = BuildInsertCommandString();
                selectCommandString = BuildSelectCommandString();
            }
            catch (Exception exc)
            {
                Logger.Throwing("SqlBizObjectProvider", "SqlBizObjectProvider", exc);
            }
        }

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            base.Initialize(name, config);
            string connectionStringName = config["connectionStringName"];
            connection = ConnectionManager.Instance.OpenConnection(connectionStringName);
        }

        public Connection Connection { get { return connection; } }

        public static T CreateFromReader(DataReader reader)
        {
            Type type = typeof(T);
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            T obj = (T)Activator.CreateInstance(type,true);
            List<PropertyInfo> propertiesNonKey = new List<PropertyInfo>();
            List<DatabaseRow> rowsNonKey = new List<DatabaseRow>();
            for (int c = 0; c < rows.Count; c++)
            {
                if (rows[c].Identity == false && rows[c].PrimaryKey == false)
                {
                    propertiesNonKey.Add(properties[c]);
                    rowsNonKey.Add(rows[c]);
                }
                else
                {
                    string rowName = properties[c].Name;
                    if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                    properties[c].SetValue(obj, reader[rowName], null);
                }
            }
            try
            {
                return (T)IdentityMap.Instance[((IBizObject)obj).ID];
            }
            catch (KeyNotFoundException)
            {
                IdentityMap.Instance.Register((IBizObject)obj);
            }
            for (int c = 0; c < rowsNonKey.Count; c++)
            {
                string rowName = propertiesNonKey[c].Name;
                if (!string.IsNullOrEmpty(rowsNonKey[c].RowName)) rowName = rowsNonKey[c].RowName;
                propertiesNonKey[c].SetValue(obj, reader[rowName], null);
            }
            return obj;
        }

        public static List<T> CreateListFromReader(DataReader reader)
        {
            List<T> list = new List<T>();
            while (reader.Read())
            {
                list.Add(CreateFromReader(reader));
            }
            return list;
        }

        private static string BuildInsertCommandString()
        {
            Type type = typeof(T);
            DatabaseTable table = Table;
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            string tableName = type.Name;
            if (table.TableName != null) tableName = table.TableName;
            string sql = "INSERT INTO ";
            StringBuilder strRows = new StringBuilder();
            StringBuilder values = new StringBuilder();
            bool coma = false;
            for (int c = 0; c < rows.Count; c++)
            {
                if (rows[c].Identity == false)
                {
                    string rowName = properties[c].Name;
                    if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                    if (coma)
                    {
                        strRows.Append(",");
                        values.Append(",");
                    }
                    else coma = true;
                    strRows.Append(rowName);
                    values.Append("@" + rowName);
                }
            }
            sql += tableName + "(" + strRows.ToString() + ") VALUES(" + values.ToString() + ")";
            return sql;
        }
        private static string BuildUpdateCommandString()
        {
            Type type = typeof(T);
            DatabaseTable table = Table;
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            string tableName = type.Name;
            if (table.TableName != null) tableName = table.TableName;
            string sql = "UPDATE ";
            StringBuilder set = new StringBuilder();
            StringBuilder where = new StringBuilder();
            bool comaSet = false;
            bool comaWhere = false;
            for (int c = 0; c < rows.Count; c++)
            {
                string rowName = properties[c].Name;
                if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                if (rows[c].Identity == false && rows[c].PrimaryKey == false)
                {
                    if (comaSet) set.Append(",");
                    else comaSet = true;
                    set.Append(rowName + "=@" + rowName);
                }
                else
                {
                    if (comaWhere) where.Append(" AND ");
                    else comaWhere = true;
                    where.Append(rowName + "=@" + rowName);
                }
            }
            sql += tableName + " SET " + set.ToString() + " WHERE " + where.ToString();
            return sql;
        }
        private static string BuildDeleteCommandString()
        {
            Type type = typeof(T);
            DatabaseTable table = Table;
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            string tableName = type.Name;
            if (table.TableName != null) tableName = table.TableName;
            string sql = "DELETE FROM ";
            StringBuilder where = new StringBuilder();
            bool comaWhere = false;
            for (int c = 0; c < rows.Count; c++)
            {
                if (rows[c].Identity == true || rows[c].PrimaryKey == true)
                {
                    string rowName = properties[c].Name;
                    if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                    if (comaWhere) where.Append(" AND ");
                    else comaWhere = true;
                    where.Append(rowName + "=@" + rowName);
                }
            }
            sql += tableName + " WHERE " + where.ToString();
            return sql;
        }
        private static string BuildSelectCommandString()
        {
            Type type = typeof(T);
            DatabaseTable table = DatabaseTable.GetDatabaseTable(type);
            List<PropertyInfo> properties = new List<PropertyInfo>();
            List<DatabaseRow> rows = DatabaseRow.GetDatabaseRows(type, ref properties);
            string tableName = type.Name;
            if (table.TableName != null) tableName = table.TableName;
            string sql = "SELECT * FROM ";
            StringBuilder where = new StringBuilder();
            bool comaWhere = false;
            for (int c = 0; c < rows.Count; c++)
            {
                if (rows[c].Identity == true || rows[c].PrimaryKey == true)
                {
                    string rowName = properties[c].Name;
                    if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                    if (comaWhere) where.Append(" AND ");
                    else comaWhere = true;
                    where.Append(rowName + "=@" + rowName);
                }
            }
            sql += tableName + " WHERE " + where.ToString();
            return sql;
        }

        public override bool Create(T obj)
        {
            Command command = Connection.CreateCommand(insertCommandString);
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            List<PropertyInfo> propertiesNonKey = new List<PropertyInfo>();
            List<DatabaseRow> rowsNonKey = new List<DatabaseRow>();
            PropertyInfo identity = null;
            for (int c = 0; c < rows.Count; c++)
            {
                if (rows[c].Identity)
                {
                    identity = properties[c];
                }
                else
                {
                    string rowName = properties[c].Name;
                    if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                    command.AddParameter("@"+rowName,properties[c].GetValue(obj,null));
                }
            }
            int result = command.ExecuteNonQuery();
            command.Dispose();
            if (result > 0 && identity != null)
            {
                command = Connection.CreateCommand("SELECT @@IDENTITY");
                int lastId = (int)command.ExecuteScalar();
                command.Dispose();
                identity.SetValue(obj, lastId, null);
            }
            return result > 0;
        }
        public override bool Update(T obj)
        {
            Command command = Connection.CreateCommand(updateCommandString);
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            List<string> whereName = new List<string>();
            List<object> whereValue = new List<object>();
            for (int c = 0; c < rows.Count; c++)
            {
                string rowName = properties[c].Name;
                if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                if (rows[c].Identity == false && rows[c].PrimaryKey == false)
                {
                    command.AddParameter("@"+rowName, properties[c].GetValue(obj, null));
                }
                else
                {
                    whereName.Add("@" + rowName);
                    whereValue.Add(properties[c].GetValue(obj, null));
                }
            }
            for (int c = 0; c < whereValue.Count; c++)
            {
                command.AddParameter(whereName[c], whereValue[c]);
            }
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result > 0;
        }
        public override bool Delete(ObjectID oid)
        {
            Command command = Connection.CreateCommand(deleteCommandString);
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            for (int c = 0; c < rows.Count; c++)
            {
                if (rows[c].Identity == true || rows[c].PrimaryKey == true)
                {
                    string rowName = properties[c].Name;
                    if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                    command.AddParameter("@" + rowName, oid[rows[c].PrimaryKeyIndex]);
                }
            }
            int result = command.ExecuteNonQuery();
            command.Dispose();
            if (result > 0)
            {
                IdentityMap.Instance.Unregister(oid);
            }
            return result > 0;
        }
        public override T Read(ObjectID oid)
        {
            Command command = Connection.CreateCommand(selectCommandString);
            List<PropertyInfo> properties = Properties;
            List<DatabaseRow> rows = Rows;
            for (int c = 0; c < rows.Count; c++)
            {
                if (rows[c].Identity == true || rows[c].PrimaryKey == true)
                {
                    string rowName = properties[c].Name;
                    if (!string.IsNullOrEmpty(rows[c].RowName)) rowName = rows[c].RowName;
                    command.AddParameter("@" + rowName, oid[rows[c].PrimaryKeyIndex]);
                    
                }
            }
            return CreateFromCommand(command);
        }
        public override List<T> ReadAll() { return ReadAll(null); }
        public List<T> ReadAll(string order)
        {
            string query = "SELECT * FROM " + Table.TableName;
            if (!string.IsNullOrEmpty(order))
            {
                query += " ORDER BY " + order;
            }
            Command command = Connection.CreateCommand(query);
            return CreateListFromCommand(command);
        }

        protected T CreateFromCommand(Command command, bool disposeCommand)
        {
            DataReader reader = command.ExecuteReader();
            object obj = null;
            if(reader.Read()) obj = CreateFromReader(reader);
            reader.Close();
            if (disposeCommand) command.Dispose();
            return (T)obj;
        }
        protected List<T> CreateListFromCommand(Command command, bool disposeCommand)
        {
            DataReader reader = command.ExecuteReader();
            List<T> list = CreateListFromReader(reader);
            reader.Close();
            if (disposeCommand) command.Dispose();
            return list;
        }
        protected T CreateFromCommand(Command command)
        {
            return CreateFromCommand(command,true);
        }
        protected List<T> CreateListFromCommand(Command command)
        {
            return CreateListFromCommand(command,true);
        }
    }
}
